package org.cncitrus.data.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.jasper.tagplugins.jstl.core.Param;
import org.cncitrus.data.common.Params;
import org.cncitrus.data.manager.ParamsManager;

public class ChinaYieldConn {//中国产量
	private Statement stmt;
	private String TABLE_NAME = "orange_z_chanliang";
	public ChinaYieldConn(){
		try {
			stmt = OrangeConn.getInstance().getStatement();
		} catch (SQLException e) {
			System.err.print("ChinaYieldConn:无法实例化Statement");
			e.printStackTrace();
		}
	}
	
	public String load(String[] year,String[] pro) throws Exception{		
		String sql = "select * from "+TABLE_NAME+" where "+Params.ChinaYield.year+" in "+ParamsManager.Array2String(year)+"  AND "+Params.ChinaYield.pro+" in"+ParamsManager.Array2String(pro);	
//		sql = "select * from orange_z_chanliangwhere year in '2002,2003'  AND pro in '湖北,全国'";
		System.out.println(sql);
		
		ResultSet rs = stmt.executeQuery(sql);
		
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		rs.last();
		sb.append("<resp count=\""+rs.getRow()+"\">");
		rs.beforeFirst();
		while(rs.next()){
			sb.append("<item ");
			sb.append("year=\""+rs.getString(Params.ChinaYield.year).trim()+"\" ");
			sb.append("pro=\""+rs.getString(Params.ChinaYield.pro).trim()+"\" ");
			sb.append("t1=\""+rs.getString(Params.ChinaYield.t1).trim()+"\" ");
			sb.append("t2=\""+rs.getString(Params.ChinaYield.t2).trim()+"\" ");
			sb.append("t3=\""+rs.getString(Params.ChinaYield.t3).trim()+"\" ");
			sb.append("t4=\""+rs.getString(Params.ChinaYield.t4).trim()+"\" ");
			sb.append("t5=\""+rs.getString(Params.ChinaYield.t5).trim()+"\"/>");
		};
		sb.append("</resp>");
		return sb.toString();
	}
	
	public String insert(String[] year,String[] pro,String[] t1,String[] t2,String[] t3,String[] t4,String[] t5) throws Exception{
		
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO "+TABLE_NAME+" ("+Params.ChinaYield.year+","+Params.ChinaYield.pro+","+Params.ChinaYield.t1+","+Params.ChinaYield.t2+","+Params.ChinaYield.t3+","+Params.ChinaYield.t4+","+Params.ChinaYield.t5+") VALUES ");		
		
		String content = ParamsManager.getInsertString(year,pro,t1,t2,t3,t4,t5);
		sb.append(content);
		stmt.execute(sb.toString());
		sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<resp rc=\"0\"/>");		
		return sb.toString();
	}
	public boolean update(){
		return false;
	}
	public boolean delete(String id){		
		return false;
	}
	
}
